from sqlite3 import IntegrityError, DatabaseError 

from .database import create_curs 
from model.creature import Creature 
from error import Missing, Duplicate 


def create_table(): 
    with create_curs() as curs: 
        qry = """
               create table if not exists creature(
                    name text primary key, 
                    country text, 
                    area text, 
                    description text, 
                    aka text 
               )
            """
        curs.execute(qry)


def row_to_model(row: tuple) -> Creature: 
    name, country, area, description, aka = row 
    return Creature(name=name, country=country, description=description, aka=aka,area=area)


def get_one(name: str) -> Creature: 
    qry = 'select * from creature where name = :name'
    params = {'name': name}
    with create_curs() as curs: 
        curs.execute(qry, params) 
        row = curs.fetchone()
        if not row: 
            raise Missing(f'Creature: {name} is not found')
        return row_to_model(row)
    

def create(creature: Creature) -> Creature: 
    qry = 'insert into creature(name, country, area, description, aka) values (' \
            ':name, :country, :area, :description, :aka)'
    params = creature.model_dump()

    with create_curs() as curs: 
        try:
            curs.execute(qry, params) 
        except IntegrityError: 
            raise Duplicate(f'Creature {creature.name} is exists')
        
    return get_one(creature.name)

def get_all() -> list[Creature]: 
    qry = 'select * from creature' 
    with create_curs() as curs: 
        curs.execute(qry) 
        if rows:= curs.fetchall(): 
            return [row_to_model(row) for row in rows] 
        
        raise Missing(f'Creature: not found')

def modify(name: str, creature: Creature) -> Creature: 
    get_one(name)

    qry = 'update  creature ' \
            'set name = :name, ' \
                ' country = :country, ' \
                ' area = :area, ' \
                ' description = :description, ' \
                ' aka = :aka ' \
            ' where name = :nameorig'
    params = creature.model_dump() 
    params['nameorig'] = name 

    with create_curs() as curs: 
        try: 
            curs.execute(qry, params) 
            
        except IntegrityError as e: 
            raise Duplicate(f'Creature: {creature.name} is exists')

    return get_one(creature.name)

def delete(name: str) -> None: 
    get_one(name) 
    with create_curs() as curs: 
        curs.execute('delete from creature where name = :name', {'name': name})
        


create_table()